/***************************************************************
*  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
*  All rights reserved.
*
*  This file is part of the GroupMe! Project. Source code of 
*  this project is closed and redistribution of this code is
*  prohibited. 
*  
*  Contact: http://www.groupme.net
*
*  This copyright notice MUST APPEAR in all copies of the file!
***************************************************************/
package net.groupme.search.utils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.groupme.model.Group;
import net.groupme.model.MODEL_CONSTANTS;
import net.groupme.model.ObjectFactory;
import net.groupme.utils.sql.SelectQueryUtility;

/**
 * created on Jul 23, 2008
 * 
 * This class provides some methods that are interesting for search, e.g.
 * a method that returns the resources that were accessed (clicked) most often, etc.
 * 
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * @author last edited by: $Author: nicole $
 * 
 * @version $Revision: 1.2 $ $Date: 2008-10-29 18:45:50 $
 */
public class SearchUtility {

	/**
	 * Returns a list of groups (DB-version not in-memory) ordered by number of clicks that were 
	 * observed by the GroupMe! system (see LogEventNavigation table).
	 * 
	 * @param topk the top k groups that should be returned
	 * @param minGroupSize minimum size of group (min number of resources in group)
	 * @return a list of groups (DB-version not in-memory) ordered by number of clicks that were 
	 * observed by the GroupMe! system (see LogEventNavigation table).
	 */
	public static final List<Group> getMostPopularGroupsAccordingToClickFrequency(int topk, int minGroupSize){
		List<Group> popularGroups = new ArrayList<Group>();
		ResultSet resources = SelectQueryUtility.executeQuery("Select r.id FROM Resource as r, LogEventNavigation as log, (Select groupId, count(*) as numRes from groupResource group by groupId) as gr  where r.type = '" + MODEL_CONSTANTS.RESOURCE_TYPE_GROUP +  "' AND log.action = 'resourceclick' and r.id = log.resourceId and r.id = gr.groupId AND gr.numRes >= " + minGroupSize + " group by log.resourceId order by count(*) DESC limit " + topk);
		try { 
			while(resources.next()){
				popularGroups.add(ObjectFactory.getGroup(resources.getString(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return popularGroups;
	}
	
	/**
	 * Returns a list of randomly selected groups (DB-version not in-memory) 
	 * 
	 * @param numberOfGroups the number of groups that should be returned
	 * @param minGroupSize minimum size of group (min number of resources in group)
	 * @return a list of randomly selected groups (DB-version not in-memory) 
	 */
	public static final List<Group> getRandomGroups(int numberOfGroups, int minGroupSize){
		List<Group> popularGroups = new ArrayList<Group>();
		ResultSet resources = SelectQueryUtility.executeQuery("Select r.id FROM Resource as r, (Select groupId, count(*) as numRes from groupResource group by groupId) as gr  where r.type = '" + MODEL_CONSTANTS.RESOURCE_TYPE_GROUP +  "' and r.id = gr.groupId AND gr.numRes >= " + minGroupSize + " order by rand() limit " + numberOfGroups);
		try { 
			while(resources.next()){
				popularGroups.add(ObjectFactory.getGroup(resources.getString(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return popularGroups;
	}
	
	/**
	 * Returns a list of latest selected groups (DB-version not in-memory) 
	 * 
	 * @param startPosition the position to start returning groups from the DB
	 * @param numberOfGroups the number of groups that should be returned
	 * @param minGroupSize minimum size of group (min number of resources in group)
	 * @return a list of randomly selected groups (DB-version not in-memory) 
	 */
	public static final List<Group> getLatestGroups(int startPosition, int numberOfGroups, int minGroupSize){
		List<Group> popularGroups = new ArrayList<Group>();
		ResultSet resources = SelectQueryUtility.executeQuery("Select r.id FROM Resource as r, (Select groupId, count(*) as numRes from groupResource group by groupId) as gr  where r.type = '" + MODEL_CONSTANTS.RESOURCE_TYPE_GROUP +  "' and r.id = gr.groupId AND gr.numRes >= " + minGroupSize + " order by r.id desc  limit " + startPosition + "," + numberOfGroups);
		try { 
			while(resources.next()){
				popularGroups.add(ObjectFactory.getGroup(resources.getString(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return popularGroups;
	}
	
	/**
	 * Returns the number of all latest groups
	 * @param minGroupSize minimum size of group (min number of resources in group)
	 * @return the number of all latest groups
	 */
	public static final int getNumberofLatestGroups(int minGroupSize){
		
		ResultSet resources = SelectQueryUtility.executeQuery("Select count(r.id) as number FROM Resource as r, (Select groupId, count(*) as numRes from groupResource group by groupId) as gr  where r.type = '" + MODEL_CONSTANTS.RESOURCE_TYPE_GROUP +  "' and r.id = gr.groupId AND gr.numRes >= " + minGroupSize);
		int number = 0;
		try {
			while(resources.next()){
				number = resources.getInt("number");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return number;
	}
}
